package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class LoginDemo {
    public static final String URL = "jdbc:mysql://localhost:3306/mydb1?serverTimezone=GMT";
    public static final String USER = "root";
    public static final String PASS = "1234";

    public static boolean login(String username, String password) {
        String sql = "SELECT * FROM tb_user WHERE username = ? AND password = ?";
        try (Connection connection = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入用户名: ");
        String username = scanner.nextLine();
        System.out.print("请输入密码: ");
        String password = scanner.nextLine();

        if (login(username, password)) {
            System.out.println("登录成功");
            showMenu(scanner);
        } else {
            System.out.println("登录失败");
        }
        scanner.close();
    }

    private static void showMenu(Scanner scanner) {
        while (true) {
            System.out.println("\n请选择操作：");
            System.out.println("1）显示所有学生");
            System.out.println("2）添加学生");
            System.out.println("3）删除学生");
            System.out.println("4）更新学生");
            System.out.println("5）退出");
            System.out.print("请输入选项: ");
            int choice = scanner.nextInt();
            scanner.nextLine(); // Consume newline

            switch (choice) {
                case 1:
                    displayStudents();
                    break;
                case 2:
                    addStudent(scanner);
                    break;
                case 3:
                    deleteStudent(scanner);
                    break;
                case 4:
                    updateStudent(scanner);
                    break;
                case 5:
                    System.out.println("退出系统");
                    return;
                default:
                    System.out.println("无效选项，请重新选择");
            }
        }
    }

    private static void displayStudents() {
        String sql = "SELECT * FROM student";
        try (Connection connection = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                System.out.println("ID: " + resultSet.getInt("id") +
                                   ", 姓名: " + resultSet.getString("name") +
                                   ", 年龄: " + resultSet.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void addStudent(Scanner scanner) {
        System.out.print("请输入学生姓名: ");
        String name = scanner.nextLine();
        System.out.print("请输入学生年龄: ");
        int age = scanner.nextInt();
        scanner.nextLine(); // Consume newline

        String sql = "INSERT INTO student (name, age) VALUES (?, ?)";
        try (Connection connection = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.executeUpdate();
            System.out.println("学生添加成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void deleteStudent(Scanner scanner) {
        System.out.print("请输入要删除的学生ID: ");
        int id = scanner.nextInt();
        scanner.nextLine(); // Consume newline

        String sql = "DELETE FROM student WHERE id = ?";
        try (Connection connection = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, id);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("学生删除成功");
            } else {
                System.out.println("未找到该学生");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void updateStudent(Scanner scanner) {
        System.out.print("请输入要更新的学生ID: ");
        int id = scanner.nextInt();
        scanner.nextLine(); // Consume newline
        System.out.print("请输入新的学生姓名: ");
        String name = scanner.nextLine();
        System.out.print("请输入新的学生年龄: ");
        int age = scanner.nextInt();
        scanner.nextLine(); // Consume newline

        String sql = "UPDATE student SET name = ?, age = ? WHERE id = ?";
        try (Connection connection = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setInt(3, id);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("学生更新成功");
            } else {
                System.out.println("未找到该学生");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}